package musicstore.com.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import musicstore.com.dao.AlbumDao;
import musicstore.com.dao.BaseDao;
import musicstore.com.entity.Album;

public class AlbumDaoImpl extends BaseDao implements AlbumDao{

/*	public List<Album> getAlbumInfo() {
		List<Album> list = new ArrayList<Album>();
		Connection conn = null;
		PreparedStatement stmt = null ;
		ResultSet rs = null ;
		
		try {
			conn = openConnetion();
			String sql = "select * from album limit 9";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				int genreID = rs.getInt("genreid");
				String artist = rs.getString("artist");
				String title = rs.getString("title");
				double price = rs.getDouble("price");
				int stock = rs.getInt("stock");
				Date dateReleased = rs.getDate("datereleased");
				String description = rs.getString("description");
				Album album = new Album(id, genreID, artist, title, price, stock, dateReleased, description);
				list.add(album);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection(conn, stmt, rs);
		}
		return list;
	}

	public List<Album> getAlbumInfoByGenreID(int gid) {
		List<Album> list = new ArrayList<Album>();
		
		Connection conn = null ;
		PreparedStatement stmt = null ;
		ResultSet rs = null ;
		
		try {
			conn = openConnetion();
			String sql = "SELECT * FROM album WHERE GenreId = ?";
			stmt = conn.prepareStatement(sql); 
			stmt.setInt(1, gid);
			rs = stmt.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String artist = rs.getString("artist");
				String title = rs.getString("title");
				double price = rs.getDouble("price");
				int stock = rs.getInt("stock");
				Date dateReleased = rs.getDate("datereleased");
				String description = rs.getString("description");
				Album album = new Album(id, gid, artist, title, price, stock, dateReleased, description);
				list.add(album);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection(conn, stmt, rs);
		}
		
		return list;
	}
	
	//id有唯一性，不需要再传入genreid
	public Album getAlbumInfoByIdAndGenreID(int gid, int id) {
		Album album = null;
		Connection conn = null ;
		PreparedStatement stmt = null ;
		ResultSet rs = null ;
		
		try {
			conn = openConnetion();
			String sql = "select * from album where genreid = ? and id = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, gid);
			stmt.setInt(2, id);
			rs = stmt.executeQuery();
			if(rs.next()){
				String artist = rs.getString("artist");
				String title = rs.getString("title");
				double price = rs.getDouble("price");
				int stock = rs.getInt("stock");
				Date dateReleased = rs.getDate("datereleased");
				String description = rs.getString("description");
				album = new Album(id, gid, artist, title, price, stock, dateReleased, description);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection(conn, stmt, rs);
		}
		
		return album;
	}
*/

	public List<Album> getAll(int gid, String title, int pageSize, int page) {
		List<Album> list = new ArrayList<Album>();
		Connection conn = null ;
		PreparedStatement stmt = null ;
		ResultSet rs = null ;
		String sqlA = "select * from album where 1 = 1 ";
		ArrayList sqlList = new ArrayList();
		if(gid>0){
			sqlA += "and genreid = ?";
			sqlList.add(gid);
		}
		if(title !=null && !title.isEmpty()){
			sqlA += "and title like ?";
			sqlList.add("%"+title+"%");
		}
		String sqlB = " order by id limit ?,?";
		sqlList.add((page-1)*pageSize);
		sqlList.add(pageSize);
		String sql = sqlA + sqlB;
		
		this.openConnection();
		try {
			rs = this.executeQuery(sql, sqlList.toArray());
			while(rs.next()){
				int id = rs.getInt("id");
				int genreID = rs.getInt("genreid");
				String artist = rs.getString("artist");
				String titles = rs.getString("title");
				double price = rs.getDouble("price");
				int stock = rs.getInt("stock");
				Date dateReleased = rs.getDate("datereleased");
				String description = rs.getString("description");
				Album album = new Album(id, genreID, artist, titles, price, stock, dateReleased, description);
				list.add(album);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeResource();
		}
		return list;
	}

	public List<Album> getAlbumInfo() {
		List<Album> list = new ArrayList<Album>();
		String sql = "select * from album";
		ResultSet rs = null;
		
		this.openConnection();
		try {
			rs = executeQuery(sql, null);
			while(rs.next()){
				int id = rs.getInt("id");
				int genreID = rs.getInt("genreid");
				String artist = rs.getString("artist");
				String title = rs.getString("title");
				double price = rs.getDouble("price");
				int stock = rs.getInt("stock");
				Date dateReleased = rs.getDate("datereleased");
				String description = rs.getString("description");
				Album album = new Album(id, genreID, artist, title, price, stock, dateReleased, description);
				list.add(album);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeResource();
		}
		
		return list;
	}

	public List<Album> getAlbumInfoByGenreID(int gid) {
		List<Album> list = new ArrayList<Album>();
		String sql = "select * from album where 1=1";
		ResultSet rs = null;
		ArrayList alist = new ArrayList();
		if(gid>0){
			sql += " and genreid = ?";
			alist.add(gid);
		}
		
		this.openConnection();
		try {
			rs = executeQuery(sql, alist.toArray());
			while(rs.next()){
				int id = rs.getInt("id");
				int genreID = rs.getInt("genreid");
				String artist = rs.getString("artist");
				String title = rs.getString("title");
				double price = rs.getDouble("price");
				int stock = rs.getInt("stock");
				Date dateReleased = rs.getDate("datereleased");
				String description = rs.getString("description");
				Album album = new Album(id, genreID, artist, title, price, stock, dateReleased, description);
				list.add(album);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeResource();
		}
		
		return list;
	}

	public Album getAlbumInfoByIdAndGenreID(int id) {
		Album a = null;
		String sql = "select * from album where id = ?";
		ResultSet rs = null;
		ArrayList alist = new ArrayList();
		alist.add(id);
		
		this.openConnection();
		try {
			rs = executeQuery(sql, alist.toArray());
			while(rs.next()){
				int ids = rs.getInt("id");
				int genreID = rs.getInt("genreid");
				String artist = rs.getString("artist");
				String title = rs.getString("title");
				double price = rs.getDouble("price");
				int stock = rs.getInt("stock");
				Date dateReleased = rs.getDate("datereleased");
				String description = rs.getString("description");
				a = new Album(ids, genreID, artist, title, price, stock, dateReleased, description);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeResource();
		}
		
		return a;
	}

	public int getAlbumRows(int gid, String title) {
		String sql = "select count(*) from album where 1 = 1 ";
		ArrayList list = new ArrayList();
		if(gid>0){
			sql+= "and genreid = ? ";
			list.add(gid);
		}
		if(title!=null){
			sql+="and title like ?";
			list.add("%"+title+"%");
		}
		ResultSet rs = null;
		int rows = 0;
		
		this.openConnection();
		try {
			rs = executeQuery(sql, list.toArray());
			if(rs.next()){
				rows = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeResource();
		}
		return rows;
	}


public static void main(String[] args) {
	int rows = new AlbumDaoImpl().getAlbumRows(0, "");
	System.out.println(rows);
	List<Album> list = new AlbumDaoImpl().getAll(1, "", 3, 1);
	for(Album a : list ){
		System.out.println(a.getArtist()+"\t"+a.getTitle());
	}
}


	
}
